﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using Common;

using System.Data.OleDb;

namespace DAL
{
    public class Invoice
    {
        public Dat_Invoice SelectInvoice(int InvoiceKey)
        {
            CIMDataContext db = new CIMDataContext();

            return db.Dat_Invoice.Single(i => i.InvoiceKey == InvoiceKey);
        }

        //添加费用
        public bool AddInvoice(Dat_Invoice invoice)
        {
            bool Success;
            CIMDataContext db = new CIMDataContext();

            db.Dat_Invoice.InsertOnSubmit(invoice);
            try
            {
                db.SubmitChanges();
                Success = true;
            }
            catch (Exception ex)
            {
                Success = false;
            }
            return Success;
        }

        public void DelInvoice(int InvoiceKey)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Invoice invoice = db.Dat_Invoice.Single(i => i.InvoiceKey == InvoiceKey);
            db.Dat_Invoice.DeleteOnSubmit(invoice);
            db.SubmitChanges();
        }

        //修改发票
        public bool EditInvoice(Dat_Invoice invoice)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Invoice Invoice = db.Dat_Invoice.Single(i => i.InvoiceKey == invoice.InvoiceKey);

            Invoice.DanNum = invoice.DanNum;
            Invoice.CustName = invoice.CustName;
            Invoice.InvoiceDate = invoice.InvoiceDate;
            Invoice.InvoiceNO = invoice.InvoiceNO;
            Invoice.InvoiceContent = invoice.InvoiceContent;
            Invoice.InvoicePrice = invoice.InvoicePrice;
            Invoice.ArriveDate = invoice.ArriveDate;
            Invoice.MUserKey = invoice.MUserKey;
            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //修改发票预约序号
        public bool EditInvoiceNO(Dat_Invoice invoice)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Invoice Invoice = db.Dat_Invoice.Single(i => i.InvoiceKey == invoice.InvoiceKey);

            Invoice.DanNum = invoice.DanNum;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //条件查询
        public IQueryable<Dat_Invoice> SelectInvoice(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_Invoice> Query;

            if (QueryTable == null)
            {
                Query = from i in db.Dat_Invoice
                        orderby i.InvoiceKey descending
                        select i;
            }
            else
            {
                Query = from i in GetConditionQuery(QueryTable)
                        orderby i.InvoiceKey descending
                        select i;
            }
            return Query;
        }

        private IQueryable<Dat_Invoice> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            var predicate = PredicateBuilder.True<Dat_Invoice>();

            if (QueryTable["CustName"] != null)
                predicate = predicate.And(i => i.CustName.Contains(QueryTable["CustName"].ToString().Trim()));
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(i => 1 == 1);
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(i => i.InvoiceDate >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(i => i.InvoiceDate <= TwoDate);
                }
                catch (Exception ex) { }
            }

            return db.Dat_Invoice.Where(predicate);
        }

        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Dat_Invoice> Query = new Invoice().SelectInvoice(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [发票信息]([预约序号] Text,[发票号码] Text,[客户名称] Text,[发票内容] Text,[开票日期] Text,[金额] Text,[送达日期] Text,[经手人] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [发票信息$] ([预约序号], [发票号码],[客户名称],[发票内容],[开票日期],[金额],[送达日期],[经手人]) VALUES (?,?,?,?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 8; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Dat_Invoice invoice in Query)
                {
                    cmd.Parameters[0].Value = string.IsNullOrEmpty(invoice.DanNum) ? "" : invoice.DanNum;
                    cmd.Parameters[1].Value = string.IsNullOrEmpty(invoice.InvoiceNO) ? "" : invoice.InvoiceNO;
                    cmd.Parameters[2].Value = string.IsNullOrEmpty(invoice.CustName) ? "" : invoice.CustName;
                    cmd.Parameters[3].Value = string.IsNullOrEmpty(invoice.InvoiceContent) ? "" : invoice.InvoiceContent;
                    cmd.Parameters[4].Value = invoice.InvoiceDate == null ? "" : Convert.ToDateTime(invoice.InvoiceDate).ToString("yyyy-MM-dd");
                    cmd.Parameters[5].Value = invoice.InvoicePrice == null  ? "" : invoice.InvoicePrice.ToString();
                    cmd.Parameters[6].Value = invoice.ArriveDate == null ? "" : Convert.ToDateTime(invoice.ArriveDate).ToString("yyyy-MM-dd");
                    cmd.Parameters[7].Value = invoice.MUserKey == null ? "" : new Account().GetAccountName(invoice.MUserKey);

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }
}
